﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using BiffenService.Model;
using System.Data;
using BiffenService.Db.Exceptions;

namespace BiffenService.Db
{
    public class BookingDb : IBookingDb
    {
        private static SqlCommand dbBooking = null;

        /// <summary>
        /// A method that inserts a booking into the database
        /// </summary>
        /// <param name="Status">int Status the status of the booking, if it is sold or reserved</param>
        /// <param name="PhoneNumber">int PhoneNumber the customers phonenumber</param>
        /// <param name="DiscountType">int DiscountType the type of the discount if it is study(1), senior(2) or none(3)</param>
        /// <param name="ShowId">int ShowId the Id of show wanted to reserve</param>
        /// <param name="Seats">List seats a List with all the seats wanted to reserve</param>
        /// <param name="MemberId">int MemberId the id of the member if the reservation is for a member</param>
        /// <returns>int result, the number of rows affected in the sql database</returns>
        public int CreateBooking(int Status, int PhoneNumber, int DiscountType, int ShowId, List<int> Seats, int MemberId)
        {

            // build flat list of reserved seats
            List<int> reservedSeats = new List<int>();
            List<Booking> bookings = GetBookingsForShow(ShowId);
            foreach (var booking in bookings)
            {
                if (booking.Status != Booking.States.Released)
                {
                    reservedSeats.AddRange(booking.Seats);
                }
            }

            // cross-check selected seats with existing reservations
            foreach (var seat in Seats)
            {
                if(reservedSeats.Contains(seat)) {
                    throw new SeatAlreadyReservedException();
                }
            }

            int result = -1;
            
            SqlTransaction transac = DbClient.BeginTransaction();

            try
            {
                object mid = DBNull.Value;
                if (MemberId != 0)
                {
                    mid = MemberId;
                }
                string sql = @"insert into bif_booking (Status, Phone, Discount, Show, Member)
                    VALUES(@status, @phone, @discount, @show, @member ) SET @Id = SCOPE_IDENTITY()";

                SqlCommand cmd = DbClient.GetDbCommand(sql);
                cmd.Parameters.AddWithValue("@status", Status);
                cmd.Parameters.AddWithValue("@phone", PhoneNumber);
                cmd.Parameters.AddWithValue("@discount", DiscountType);
                cmd.Parameters.AddWithValue("@show", ShowId);
                cmd.Parameters.AddWithValue("@member", mid);

                cmd.Parameters.Add("@Id", SqlDbType.Int).Direction = ParameterDirection.Output;
                result = cmd.ExecuteNonQuery();
                int id = (int)cmd.Parameters["@Id"].Value;

                createSeatsForBooking(Seats, id);
                transac.Commit();
            }

            catch (SqlException sqlE)
            {
                transac.Rollback();
                Console.WriteLine("Sql error goddammit! in createBooking" + sqlE.Message);
                return -1;
            }
            catch (Exception e)
            {
                transac.Rollback();
                Console.WriteLine("Ordinary Error in createBooking" + e.Message);
                return -1;
            }
            finally
            {
                DbClient.Close();
            }

            return result;
        }

        /// <summary>
        /// Fetch bookings for a show by the show id
        /// </summary>
        /// <param name="showId"></param>
        /// <returns></returns>
        public List<Booking> GetBookingsForShow(int showId)
        {
            Show show = new Show()
            {
                Id = showId
            };

            return GetBookingsForShow(show);

        }

        /// <summary>
        /// A method that finds all bookings for a given show
        /// </summary>
        /// <param name="show">Show show the show wanted to find the bookings for</param>
        /// <returns>List bookings a List with all bookings found for a specific show</returns>
        public List<Booking> GetBookingsForShow(Show show)
        {
            String sql = "SELECT * FROM bif_booking WHERE Show = @showId";

            SqlCommand command = DbClient.GetDbCommand(sql);

            command.Parameters.AddWithValue("@showId", show.Id);

            IDataReader r = command.ExecuteReader();

            List<Booking> bookings = new List<Booking>();

            while (r.Read())
            {
                int id = int.Parse(r["Id"].ToString());
                int status = (int)r["Status"];
                int phone = (int)(r["Phone"]);

                Booking booking = new Booking();
                booking.ID = id;
                booking.Status = (Booking.States)status;
                booking.PhoneNumber = phone;
                bookings.Add(booking);
            }
            r.Close();
            DbClient.Close();

            foreach (Booking booking in bookings)
            {
                booking.Seats = FindSeatsByBookingId(booking.ID);
            }
            return bookings;
        }

        /// <summary>
        /// Method that finds all bookings on a specific phone number from the database
        /// </summary>
        /// <param name="phone">int phone the phonenumber of the booking wanted to find</param>
        /// <returns>List bookings a List with all bookings found with a specific phonenumber</returns>
        public List<Booking> FindBookingIdByPhone(int phone)
        {
            List<int> bookingIds = new List<int>();
            List<Booking> bookings = new List<Booking>();
            try
            {
                string sql = @"select Id from bif_booking where Phone = " + phone;
                Console.WriteLine(sql);
                
                SqlCommand dbBooking = DbClient.GetDbCommand(sql);
                IDataReader dbReader = dbBooking.ExecuteReader();

                while (dbReader.Read())
                {
                    int id = (int)(dbReader["Id"]);
                    bookingIds.Add(id);
                }

                dbReader.Close();
                DbClient.Close();

                foreach (int ba in bookingIds)
                {
                    Booking booking = FindBookingById(ba);
                    bookings.Add(booking);
                }

            }

            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error goddammit! in findByPhone" + sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary Error in findByPhone" + e.Message);
            }
            return bookings;
        }

        /// <summary>
        /// A method that finds a booking from the database with a specific Id
        /// </summary>
        /// <param name="bookingId">int bookingId the id of the booking wanted to find</param>
        /// <returns>Booking b, the booking found from the database</returns>
        public Booking FindBookingById(int bookingId)
        {
            Booking b = new Booking();
            int showId = 0;
            int phone = 0;
            //discId set to 3 because it is the same as no discount, discount is set to the correct later in this method
            int discId = 3;
            try
            {
                List<int> seats = FindSeatsByBookingId(bookingId);
                string sql = @"select * from bif_booking where Id = " + bookingId;
                Console.WriteLine("SQL statement: " + sql);

                SqlCommand dbBooking = DbClient.GetDbCommand(sql);
                IDataReader dbReader = dbBooking.ExecuteReader();

                ShowDb showDb = new ShowDb();
                MovieDb movieDb = new MovieDb();
                MemberDb memberDb = new MemberDb();

                if (dbReader.Read())
                {
                    b.ID = (int)(dbReader["Id"]);
                    b.Status = (Booking.States)(dbReader["Status"]);
                    phone = (int)(dbReader["Phone"]);
                    b.PhoneNumber = phone;
                    showId = (int)(dbReader["Show"]);
                    b.Seats = seats;
                    discId = (int)(dbReader["Discount"]);
                    
                }
                dbReader.Close();
                DbClient.Close();

                Show show = showDb.GetShowsById(showId);
                show.setMovies(movieDb.GetMoviesForShow(showId));
                b.Show = show;
                
                Member member = memberDb.FindMemberByPhone(phone);
                if (member != null)
                {
                   b.Member = member;
                }

                Discount disc = GetDiscount(discId);
                b.Discount = disc;
            }
            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error goddammit! in find booking: " + sqlE.Message);
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary Error in find booking: " + e.Message);
            }
            return b;
        }

        /// <summary>
        /// Method that deletes a booking from the database
        /// </summary>
        /// <param name="bookingId"> int bookingId the Id of the booking</param>
        /// <returns>int result, the number of rows affected in the sql database</returns>
        public int DeleteBookingById(int bookingId)
        {
            int result = -1;
            try
            {
                // Delete Seats from booking
                string s = "DELETE FROM bif_booking_seat WHERE Booking=@Id";
                SqlCommand cmd = DbClient.GetDbCommand(s);
                cmd.Parameters.AddWithValue("@Id", bookingId);
                result = cmd.ExecuteNonQuery();

                if (result > 0)
                {
                    // Delete Booking
                    string b = "DELETE FROM bif_booking WHERE Id=@Id";
                    cmd = DbClient.GetDbCommand(b);
                    cmd.Parameters.AddWithValue("@Id", bookingId);
                    result = cmd.ExecuteNonQuery();
                }
                DbClient.Close();
            }
            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error goddammit! in delete booking" + sqlE.Message);
                return -1;
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary Error in an delete booking" + e.Message);
                return -1;
            }
            return result;
        }

        /// <summary>
        /// A method that inserts the seats into the database from a newly created booking
        /// </summary>
        /// <param name="seats">List seats a List with the seats reserved for the booking</param>
        /// <param name="bookingId">int bookingId the Id of the booking wanted to set the seats on</param>
        /// <returns>int result, the number of rows affected in the sql database</returns>
        public int createSeatsForBooking(List<int> seats, int bookingId)
        {
            int result = -1;
            try
            {
                foreach (int seat in seats)
                {
                    string sql = @"INSERT INTO bif_booking_seat (Booking, Seat) VALUES (@Booking, @Seat)";

                    SqlCommand command = DbClient.GetDbCommand(sql);
                    command.Parameters.AddWithValue("@Booking", bookingId);
                    command.Parameters.AddWithValue("@Seat", seat);
                    result = command.ExecuteNonQuery();
                }
            }
            catch (SqlException sqlE)
            {
                Console.WriteLine("Sql error goddammit! createSeatsForBooking" + sqlE.Message);
                return -1;
            }
            catch (Exception e)
            {
                Console.WriteLine("Ordinary Error in createSeatsForBooking" + e.Message);
                return -1;
            }
            return result;
        }

        /// <summary>
        /// A method that finds the specific seats reserved for a booking from the database
        /// </summary>
        /// <param name="bookingId">int bookingId the Id of the booking wanted to find the seats on</param>
        /// <returns>List seats a List with the seats reserved for the specific booking</returns>
        public List<int> FindSeatsByBookingId(int bookingId)
        {
            List<int> seatList = new List<int>();
            try
            {
                string sql = @"select seat from bif_booking_seat where Booking = " + bookingId;

                SqlCommand dbBooking = DbClient.GetDbCommand(sql);
                IDataReader dbReader = dbBooking.ExecuteReader();

                int Id;
                while (dbReader.Read())
                {
                    Id = (int)(dbReader["Seat"]);
                    seatList.Add(Id);
                }
                dbReader.Close();
                DbClient.Close();
            }
            catch (SqlException SqlE)
            {
                Console.WriteLine("sql Exception in findSeatsByBookingId " + SqlE.Message);
            }
            catch (Exception E)
            {
                Console.WriteLine("Exception in findSeatsByBookingId " + E.Message);
            }
            return seatList;
        }
        /// <summary>
        /// A method that updates the status on a booking to sold
        /// </summary>
        /// <param name="id">int id the Id of the booking wanted to be marked as sold </param>
        /// <returns>int result the number of rows affected in the sql database</returns>
        public int MarkBookingAsSold(int id)
        {
            int result = -1;
            try
            {
                string sql = "UPDATE bif_booking SET Status=2 WHERE Id=@Id";
                SqlCommand cmd = DbClient.GetDbCommand(sql);
                cmd.Parameters.AddWithValue("@Id", id);

                result = cmd.ExecuteNonQuery();

                DbClient.Close();
            }
            catch (SqlException SqlE)
            {
                Console.WriteLine("sql Exception in MarkBookingAsSold " + SqlE.Message);
                return -1;
            }
            catch (Exception E)
            {
                Console.WriteLine("Exception in MarkBookingAsSold " + E.Message);
                return -1;
            }
            return result;
        }

        /// <summary>
        /// Method to create a discount object with data from the database
        /// </summary>
        /// <param name="discountId">int discountId</param>
        /// <returns></returns>
        public Discount GetDiscount(int discountId)
        {
            Discount discount = new Discount();
            try
            {
                string sql = @"select * from bif_discount where Id = @Id";
                SqlCommand command = DbClient.GetDbCommand(sql);
                command.Parameters.AddWithValue("@Id", discountId);
               
                IDataReader dbReader = command.ExecuteReader();
                
                if (dbReader.Read())
                {
                    discount.DiscountId = (int)(dbReader["Id"]);
                    discount.Name = (string)(dbReader["Name"]);
                    discount.DiscountAmount = (decimal)(dbReader["Discount"]);
                }
                dbReader.Close();
                DbClient.Close();
            }
            catch (SqlException SqlE)
            {
                Console.WriteLine("sql Exception in GetDiscount " + SqlE.Message);          
            }
            catch (Exception E)
            {
                Console.WriteLine("Exception in GetDiscount: " + E.Message);
            }
            return discount;
        }

    }
}